package app

import (
	"fmt"
	"strings"

	"git.sr.ht/~mil/mobsql/logger"
	"git.sr.ht/~mil/mobsql/utilfuncs"
)

// FilterBuildWhereClause constructs the where clause to run against
// the _vsources table (which is a join of the mobility DB _mdb & the internal
// tracking _mobsql tables).
//
// Returned string is the where condition; and []any are the args for passing
// as params to sql driver.
func FilterBuildWhereClause(filter SourcesetFilter) (string, []any) {
	args := []any{}
	conds := []string{}

	if len(filter.Mdbid) > 0 {
		for _, mdbid := range filter.Mdbid {
			args = append(args, mdbid)
		}
		conds = append(conds, fmt.Sprintf(
			"_vsources.mdbid in (%s)",
			strings.Join(utilfuncs.ArrayWithValue(len(filter.Mdbid), "?"), ","),
		))
	}
	if filter.Country != "" {
		conds = append(conds, `lower(_vsources.country) like lower('%' || ? || '%')`)
		args = append(args, filter.Country)
	}
	if filter.Municipality != "" {
		conds = append(conds, `lower(_vsources.municipality) like lower('%' || ? || '%')`)
		args = append(args, filter.Municipality)
	}
	if filter.Subdivision != "" {
		conds = append(conds, `lower(_vsources.subdivision) like lower('%' || ? || '%')`)
		args = append(args, filter.Subdivision)
	}
	if filter.Provider != "" {
		conds = append(conds, `lower(_vsources.provider) like lower('%' || ? || '%')`)
		args = append(args, filter.Provider)
	}
	if filter.Name != "" {
		conds = append(conds, `lower(_vsources.name) like lower('%' || ? || '%')`)
		args = append(args, filter.Name)
	}
	if filter.Status != "" {
		conds = append(conds, `_vsources.status = ?`)
		args = append(args, filter.Status)
	}
	if filter.Glob != "" {
		conds = append(
			conds,
			`(
				lower(_vsources.country) like lower('%' || ? || '%') or 
			  lower(_vsources.municipality) like lower('%' || ? || '%') or
			  lower(_vsources.subdivision) like lower('%' || ? || '%') or
			  lower(_vsources.provider) like lower('%' || ? || '%') or
			  lower(_vsources.name) like lower('%' || ? || '%')
			 )`,
		)
		args = append(args, filter.Glob, filter.Glob, filter.Glob, filter.Glob, filter.Glob)
	}
	if filter.Loaded != nil {
		if *filter.Loaded {
			conds = append(conds, "_vsources.loaded = 1")
		} else {
			conds = append(conds, "_vsources.loaded = 0")
		}
	}
	if filter.Computed != nil {
		if *filter.Computed {
			conds = append(conds, "_vsources.computed = 1")
		} else {
			conds = append(conds, "_vsources.computed = 0")
		}
	}
	for _, coord := range filter.Coords {
		if len(coord) < 2 {
			continue
		}
		conds = append(conds, `
			min_lat is not null and min_lon is not null and 
			max_lat is not null and max_lon is not null and
			? between min_lat and max_lat and
			? between min_lon and max_lon
		`)
		args = append(args, coord[0], coord[1])
	}
	if filter.MaxKM != 0 {
		conds = append(conds, "km is not null and km < ?")
		args = append(args, filter.MaxKM)
	}

	whereClause := ""
	if len(conds) > 0 {
		whereClause = fmt.Sprintf("where 1 = 1 and %s", strings.Join(conds, " and "))
	}
	logger.Warnf("Input filter %v translated to SQL where clause: '%s' with args: %v", filter, whereClause, args)
	return whereClause, args
}
